{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# This notebook assumes to be running from your FireCARES VM (eg. python manage.py shell_plus --notebook --no-browser)\n",
    "\n",
    "import sys\n",
    "import os\n",
    "import time\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import psycopg2\n",
    "import django\n",
    "sys.path.insert(0, os.path.realpath('..'))\n",
    "import folium\n",
    "django.setup()\n",
    "from IPython.display import display\n",
    "from django.contrib.gis.geos import GEOSGeometry\n",
    "\n",
    "pd.set_option(\"display.max_rows\",100)\n",
    "\n",
    "def display_geom(geom):\n",
    "    _map = folium.Map(location=[geom.centroid.y, geom.centroid.x],\n",
    "                      tiles='Stamen Toner')\n",
    "    _map.choropleth(geo_str=geom.geojson, line_weight=0, fill_opacity=0.2, fill_color='green')\n",
    "    ll = geom.extent[1::-1]\n",
    "    ur = geom.extent[3:1:-1]\n",
    "    _map.fit_bounds([ll, ur])\n",
    "\n",
    "    return _map\n",
    "\n",
    "nfirs = psycopg2.connect('service=nfirs')\n",
    "fc = psycopg2.connect('service=firecares')\n",
    "parcels = psycopg2.connect('service=parcels')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Total departments/stations in FireCARES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "select *\n",
    "from\n",
    "    (select count(1) as total_departments from firestation_firedepartment) total_departments,\n",
    "    (select count(1) as total_stations from firestation_firestation) total_stations\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql_query(q, fc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Covered population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "select sum(fd.population) as total_population\n",
    "from firestation_firedepartment fd\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql_query(q, fc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Covered area"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from firecares.firestation.models import FireDepartment\n",
    "\n",
    "deps = FireDepartment.objects.filter(population__isnull=False).exclude(population=0)\n",
    "\n",
    "areas = filter(lambda x: x[1] is not None, map(lambda x: (x.id, x.geom_area), deps))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sum(a[1] for a in areas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "len(areas)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Total incidents and building fires"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "display(pd.read_sql_query(\"select count(1) as buildingfires from buildingfires\", nfirs))\n",
    "display(pd.read_sql_query(\"select count(1) as incidents from incidentaddress\", nfirs))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Coverage of departments based on department-owned census geometry that have populations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Number of departments with owned census tracts\n",
    "\n",
    "q = \"\"\"\n",
    "select *, (null_tracts_count + not_null_tracts_count) as total\n",
    "from\n",
    "    (select count(1) as null_tracts_count from firestation_firedepartment where owned_tracts_geom is null) null_owned_tracts,\n",
    "    (select count(1) as not_null_tracts_count from firestation_firedepartment where owned_tracts_geom is not null) not_null_owned_tracts\n",
    "    \"\"\"\n",
    "\n",
    "print \"Owned tracts geom\"\n",
    "display(pd.read_sql_query(q, fc))\n",
    "\n",
    "\n",
    "q = \"\"\"\n",
    "select *, (null_jurisdictions + not_null_jurisdictions) as total\n",
    "from\n",
    "    (select count(1) as null_jurisdictions from firestation_firedepartment where geom is null) null_jurisdiction,\n",
    "    (select count(1) as not_null_jurisdictions from firestation_firedepartment where geom is not null) not_null_jurisdiction\n",
    "\"\"\"\n",
    "print \"Jurisdictions\"\n",
    "display(pd.read_sql_query(q, fc))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "q = \"\"\"\n",
    "select ST_Union(ST_Union(fd.owned_tracts_geom), ST_Union(fd.geom))\n",
    "from firestation_firedepartment fd\n",
    "where fd.state = 'MO'\n",
    "\"\"\"\n",
    "\n",
    "df = pd.read_sql_query(q, fc)\n",
    "\n",
    "poly = GEOSGeometry(df.values[0][0])\n",
    "display(display_geom(poly.simplify()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "# Total square meters in the USA (territories excluded)\n",
    "\n",
    "us = 9147593069344.\n",
    "\n",
    "pd.set_option('display.float_format', lambda x: '%f' % x)\n",
    "\n",
    "q = \"\"\"\n",
    "select ST_Area(geography(ST_Union(ST_Union(ST_MakeValid(fd.owned_tracts_geom)), ST_Union(ST_MakeValid(fd.geom)))))\n",
    "from firestation_firedepartment fd\n",
    "where fd.population is not null and fd.population != 0\n",
    "and fd.state = 'CT'\n",
    "\"\"\"\n",
    "\n",
    "display(pd.read_sql_query(q, fc).values[0][0] / us)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Invalid geometries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "q = \"\"\"\n",
    "select count(1), state from firestation_firedepartment fd where not ST_IsValid(fd.geom) group by fd.state\n",
    "\"\"\"\n",
    "\n",
    "display(pd.read_sql_query(q, fc))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "q = \"\"\"\n",
    "select count(1), state from firestation_firedepartment fd where not ST_IsValid(fd.owned_tracts_geom) group by fd.state\n",
    "\"\"\"\n",
    "\n",
    "display(pd.read_sql_query(q, fc))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Bad address geocodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "q = \"\"\"\n",
    "select id, ST_SRID(a.geom), ST_X(a.geom), ST_Y(a.geom), a.state_province\n",
    "from firecares_core_address a\n",
    "where ST_X(a.geom) > 180 or ST_X(a.geom) < -180 or ST_Y(a.geom) > 180 or ST_Y(a.geom) < -180\n",
    "\"\"\"\n",
    "\n",
    "display(pd.read_sql_query(q, fc))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Fix bad addresses\n",
    "%%time\n",
    "\n",
    "q = \"\"\"\n",
    "update firecares_core_address\n",
    "set geom = ST_Transform(ST_SetSRID(geom, 3857), 4326)\n",
    "where ST_X(geom) > 180 or ST_X(geom) < -180 or ST_Y(geom) > 180 or ST_Y(geom) < -180\n",
    "\"\"\"\n",
    "\n",
    "c = fc.cursor()\n",
    "c.execute(q)\n",
    "fc.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Bad addresses (missing country/etc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "q = \"\"\"\n",
    "select count(1)\n",
    "from firecares_core_address\n",
    "where address_line2 = 'None'\n",
    "\"\"\"\n",
    "\n",
    "display(pd.read_sql_query(q, fc))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Verify station lat/lons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from firecares.firestation.models import FireStation\n",
    "map(lambda x: (x.get('geom').coords, x.get('name')), FireStation.objects.filter(department_id=97963).values('geom', 'name'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Jurisdiction boundary statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "from firecares.firestation.models import FireDepartment\n",
    "from firecares.utils import lenient_summation\n",
    "import json\n",
    "import os\n",
    "d = []\n",
    "fname = '/firecares/outf.json'\n",
    "\n",
    "if os.path.exists(fname):\n",
    "    d = json.load(open(fname))\n",
    "ids = map(lambda x: x.get('fcid'), d)\n",
    "\n",
    "for idx, fd in enumerate(FireDepartment.objects.defer('owned_tracts_geom').filter(archived=False)):\n",
    "    if fd.id in ids:\n",
    "        continue\n",
    "    if idx % 10 == 0:\n",
    "        print idx\n",
    "    with open(fname, 'w') as f:\n",
    "        json.dump(d, f)\n",
    "    d.append({'fcid': fd.id,\n",
    "              'name': fd.name,\n",
    "              'state': fd.state,\n",
    "              'boundary': True if fd.geom else False,\n",
    "              'population': fd.population,\n",
    "              'region': fd.region,\n",
    "              'fires_count': lenient_summation(*map(lambda x: x.get('count'), fd.metrics.residential_structure_fire_counts['all'])),\n",
    "              'casualty_count': fd.metrics.nfirs_deaths_and_injuries_sum['all'],\n",
    "              'station_count': fd.firestation_set.count()})\n",
    "\n",
    "print 'done'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "deduped = []\n",
    "for i in d:\n",
    "    if i.get('fcid') in map(lambda x: x.get('fcid'), deduped):\n",
    "        continue\n",
    "    deduped.append(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(deduped).to_csv('/tmp/stations.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
